package com.jfinal.code.eclipse.plugin.generator.db;

import java.io.File;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import com.yaolin.generator.api.Column;
import com.yaolin.generator.api.Table;

/**
 * DB 工具类
 * @author yaolin
 *
 */
public class DbUtils {
	
	/**
	 * Table Cache
	 * @author yaolin
	 *
	 */
	public static class DbInfoHolder {
		private static final Map<String, Table> cache = new HashMap<String, Table>();
		public static Map<String, Table> getCache() {
			return cache;
		}
		public static String[] listTableName() {
			try {
				List<String> list = new ArrayList<String>(cache.keySet());
				Collections.sort(list);
				return list.toArray(new String[]{});
			} catch (Exception e) {
			}
			return new String[]{};
		}
	}

	/**
	 * 获取数据库连接
	 * @param jarFile 驱动包文件
	 * @param driverClassName 驱动类全名
	 * @param url URL
	 * @param username 数据库用户名
	 * @param password 数据库密码
	 * @return
	 * @throws Exception
	 * @throws Throwable
	 */
	@SuppressWarnings({"rawtypes"}) //"resource",  JDK1.7+
	public static Connection getConnection(String jarFile, String driverClassName, String url, String username,
			String password) throws Exception, Throwable {
		File file = new File(jarFile);

		URLClassLoader loader = new URLClassLoader(new URL[] { file.toURI().toURL() },
				Thread.currentThread().getContextClassLoader());
		Class clazz = loader.loadClass(driverClassName);
		Driver driver = (Driver) clazz.newInstance();
		Properties prop = new Properties();
		prop.put("user", username);
		prop.put("password", password);
		return driver.connect(url, prop);
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param connection
	 * @throws Exception
	 */
	public static void closeConnection(Connection connection) throws Exception {
		connection.close();
		connection = null;
	}

	/**
	 * 获取数据库所有表信息
	 * 
	 * @param connection
	 * @return
	 * @throws Exception
	 */
	public static List<Table> listTable(DbType dbType, Connection connection) throws Exception {
		List<Table> listTable = new ArrayList<Table>();
		if (connection != null) {
			DbInfoHolder.cache.clear(); // empty cache

			DatabaseMetaData dbMeteData = connection.getMetaData();
			String schema = dbType == DbType.Oracle ? dbMeteData.getUserName() : null;
			ResultSet rs = dbMeteData.getTables(connection.getCatalog(), schema, null, new String[]{"TABLE"});
			while (rs.next()) {
				if (dbType == DbType.SQLServer && "sys".equals(rs.getString("TABLE_SCHEM"))) {
					continue; // 目前SQLServer，先写死， 'CHECK_CONSTRAINTS 无效'
				}
				Table table = new Table();
				// TABLE_NAME String => table name
				table.setName(rs.getString("TABLE_NAME"));
				// REMARKS String => explanatory comment on the table
				table.setRemark(rs.getString("REMARKS"));
				table.setListColumn(listColumnByTable(dbType, dbMeteData, connection, table));
				listTable.add(table);
				// put in cache
				DbInfoHolder.cache.put(table.getName(), table);
			}
			rs.close();
			rs = null;
		}
		return listTable;
	}
	
	/**
	 * 获取某个表的所有列信息
	 * @param dbType
	 * @param dbMeteData
	 * @param connection
	 * @param table 表对象
	 * @return
	 * @throws Exception
	 */
	private static List<Column> listColumnByTable(DbType dbType, DatabaseMetaData dbMeteData, Connection connection, Table table) throws Exception {
		List<Column> listColumn = new ArrayList<Column>();
		ResultSet rs = connection.createStatement().executeQuery(String.format(dbType.getSelectEmptyResultSQLTmpl(), table.getName()));
		ResultSetMetaData rsmd = rs.getMetaData();
		String schema = dbType == DbType.Oracle ? dbMeteData.getUserName() : null;
		
		Set<String> primaryKeySet = listPrimaryKeyByTableName(dbMeteData, connection, table.getName());
		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
			Column column = new Column();
			column.setTable(table);
			column.setName(rsmd.getColumnName(i));
			column.setSqlType(rsmd.getColumnType(i));
			column.setTypeName(rsmd.getColumnTypeName(i));
			column.setClassName(rsmd.getColumnClassName(i));
			column.setDisplayLenth(rsmd.getColumnDisplaySize(i));
			column.setNullable(rsmd.isNullable(i) == ResultSetMetaData.columnNullable);
			column.setAutoIncrement(rsmd.isAutoIncrement(i));
			column.setPrimaryKey(primaryKeySet.contains(column.getName()));
			ResultSet rss = dbMeteData.getColumns(connection.getCatalog(), schema, table.getName(), column.getName());
			while (rss.next()) {
				// 7.COLUMN_SIZE int => column size. 
				String value = rss.getString("COLUMN_SIZE");
				if (value != null) {
					column.setSize(Integer.valueOf(value));
				}
				// 9.DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable. 
				value = rss.getString("DECIMAL_DIGITS");
				if (value != null) {
					column.setDecimalDigits(Integer.valueOf(value));
				}
				// 12.REMARKS String => comment describing column (may be null) 
				column.setRemark(rss.getString("REMARKS"));
				// 13.COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null) 
				column.setDefaultValue(rss.getString("COLUMN_DEF"));
			}
			rss.close();
			rss = null;
		}
		rs.close();
		rs = null;
		return listColumn;
	}
	
	/**
	 * 获取某个表的主键信息
	 * @param dbMeteData
	 * @param connection
	 * @param tableName 表名
	 * @return
	 * @throws Exception
	 */
	private static Set<String> listPrimaryKeyByTableName(DatabaseMetaData dbMeteData, Connection connection, String tableName) throws Exception {
		Set<String> primaryKeySet = new HashSet<String>();
		ResultSet rs = dbMeteData.getPrimaryKeys(connection.getCatalog(), null, tableName);
		while (rs.next()) {
			primaryKeySet.add(rs.getString("COLUMN_NAME"));
		}
		rs.close();
		rs = null;
		return primaryKeySet;
	}
}
